CREATE DATABASE BD_SYS_ATENCIONES
GO
USE BD_SYS_ATENCIONES
GO

CREATE
  TABLE GRDetRequerimiento
  (
    iNumReq  INTEGER NOT NULL ,
    iCodMat  INTEGER NOT NULL ,
    nCanReq  NUMERIC (10,2) ,
    iCodUsu  INTEGER ,
    vNomTer  VARCHAR (20) ,
    dtFecAct DATETIME ,
    CONSTRAINT GRDetRequerimiento_PK PRIMARY KEY CLUSTERED (iCodMat, iNumReq)
WITH
  (
    ALLOW_PAGE_LOCKS = ON ,
    ALLOW_ROW_LOCKS  = ON
  )
  ON "default"
  )
  ON "default"
GO

CREATE
  TABLE GREstadoReq
  (
    iCodEstReq INTEGER NOT NULL ,
    vNomEstReq VARCHAR (100) NOT NULL ,
    iCodUsu    INTEGER NOT NULL ,
    vNomTer    VARCHAR (20) NOT NULL ,
    dtFecAct   DATETIME NOT NULL ,
    CONSTRAINT GREstadoReq_PK PRIMARY KEY CLUSTERED (iCodEstReq)
WITH
  (
    ALLOW_PAGE_LOCKS = ON ,
    ALLOW_ROW_LOCKS  = ON
  )
  ON "default"
  )
  ON "default"
GO

CREATE
  TABLE GRMaeRequerimiento
  (
    iNumReq    INTEGER NOT NULL ,
    dtFecReq   DATETIME NOT NULL ,
    vHorReq    VARCHAR (8) NOT NULL ,
    iCodEstReq INTEGER NOT NULL ,
    vObsReq    VARCHAR (1000) ,
    bAprobado BIT ,
    iCodUsuApro INTEGER ,
    dtFecApro   DATETIME ,
    iCodUsuAte  INTEGER ,
    dtFecAte    DATETIME ,
    vObsAte     VARCHAR (1000) ,
    iCodUsuA    INTEGER NOT NULL ,
    vNomTer     VARCHAR (20) NOT NULL ,
    dtFecAct    DATETIME NOT NULL ,
    CONSTRAINT GRMaeRequerimiento_PK PRIMARY KEY CLUSTERED (iNumReq)
WITH
  (
    ALLOW_PAGE_LOCKS = ON ,
    ALLOW_ROW_LOCKS  = ON
  )
  ON "default"
  )
  ON "default"
  GO

CREATE
  TABLE GRMovRequerimiento
  (
    iCorMovReq INTEGER NOT NULL ,
    iCodUOr    INTEGER NOT NULL ,
    iCodUsu    INTEGER NOT NULL ,
    iNumReq    INTEGER ,
    dtFecMov   DATETIME NOT NULL ,
    vObsMov    VARCHAR (1000) ,
    iCodUsuA   INTEGER NOT NULL ,
    vNomTer    VARCHAR (20) NOT NULL ,
    dtFecAct   DATETIME NOT NULL ,
    iCodUsuUOr INTEGER NOT NULL ,
    CONSTRAINT GRMovRequerimiento_PK PRIMARY KEY CLUSTERED (iCorMovReq, iCodUOr
    , iCodUsu, iCodUsuUOr)
WITH
  (
    ALLOW_PAGE_LOCKS = ON ,
    ALLOW_ROW_LOCKS  = ON
  )
  ON "default"
  )
  ON "default"  
  GO

ALTER TABLE GRDetRequerimiento
ADD CONSTRAINT GRDetRequerimiento_GRMaeRequerimiento_FK FOREIGN KEY
(
iNumReq
)
REFERENCES GRMaeRequerimiento
(
iNumReq
)
ON
DELETE
  NO ACTION ON
UPDATE NO ACTION
GO

ALTER TABLE GRMaeRequerimiento
ADD CONSTRAINT GRMaeRequerimiento_GREstadoReq_FK FOREIGN KEY
(
iCodEstReq
)
REFERENCES GREstadoReq
(
iCodEstReq
)
ON
DELETE
  NO ACTION ON
UPDATE NO ACTION
GO

ALTER TABLE GRMovRequerimiento
ADD CONSTRAINT GRMovRequerimiento_GRMaeRequerimiento_FK FOREIGN KEY
(
iNumReq
)
REFERENCES GRMaeRequerimiento
(
iNumReq
)
ON
DELETE
  NO ACTION ON
UPDATE NO ACTION

insert into GREstadoReq values(1,'ACTIVO','1','ESTACION 1','2014-01-22 02:48:49')
insert into GREstadoReq values(2,'APROBADO','2','ESTACION 2','2014-01-22 02:48:49')
insert into GREstadoReq values(3,'ANULADO','3','ESTACION 3','2014-01-22 02:48:49')
insert into GREstadoReq values(4,'ATENDIDO','3','ESTACION 3','2014-01-22 02:48:49')

